//201902104030_何琛选
package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.Degree;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;


public final class DegreeDao {
    private static Collection<Degree> degrees;
    private static DegreeDao degreeDao = null;


    private DegreeDao() {
    }

    public static DegreeDao getInstance() {
        if (degreeDao == null){
            degreeDao = new DegreeDao();
        }
        return degreeDao;
    }
    public int count(Connection connection) throws SQLException{
        return CommonDao.getInstance().count(connection,"degree");
    }

    public Collection<Degree> findAll(
            Connection connection, List<Condition> conditions_json, Pagination pagination)
            throws SQLException {
        //创建Department类型集合类对象，HashSet不能保证结果中元素的顺序
        Collection<Degree> desriedDegrees = new HashSet<Degree>();

        int totalNum = DegreeDao.getInstance().count(connection);

        //创建查询departmentd的主语句
        StringBuilder degree_sql = new StringBuilder("select * from degree");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if (conditions_json != null) {
            String whereClause = Condition.toWhereClause(conditions_json);
            degree_sql.append(whereClause);
        }
        //如果参数不为空，为SQL语句添加LIMIT子句
        if (pagination != null){
            degree_sql.append(pagination.toLimitClause(totalNum) + " ");
        }
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(degree_sql.toString());
        //获得Department对象的结果集
        ResultSet resultSet = pstmt.executeQuery();

        while (resultSet.next()) {
            //根据id查找结果创建Department对象
            Degree degree = new Degree(
                    resultSet.getInt("id"),
                    resultSet.getString("description"),
                    resultSet.getString("no"),
                    resultSet.getString("remarks")
                     );
            desriedDegrees.add(degree);
        }
        //关闭对象
        JdbcHelper.close(resultSet, pstmt);
        return desriedDegrees;
    }

    public Degree find(Integer id,Connection connection) throws SQLException {
        String deleteDegree_sql = "select * from degree where id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt =connection.prepareStatement(deleteDegree_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);
        //根据id查找学院
        ResultSet resultSet = pstmt.executeQuery();
        resultSet.next();
        //根据id查找结果创建degree对象
        Degree degree = new Degree(
                id,
                resultSet.getString("description"),
                resultSet.getString("no"),
                resultSet.getString("remarks"));
        //关闭
        JdbcHelper.close(pstmt,connection);
        return degree;
    }

    public boolean update(Degree degree,Connection connection) throws SQLException {
        String updateDegree_sql =
                "UPDATE degree SET no = ?,description = ?,remarks = ? WHERE id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(updateDegree_sql);
        //为预编译参数赋值
        pstmt.setString(1,degree.getNo());
        pstmt.setString(2,degree.getDescription());
        pstmt.setString(3,degree.getRemarks());
        pstmt.setInt(4,degree.getId());
        return pstmt.executeUpdate()>0;
    }

    public boolean add(Degree degree,Connection connection) throws SQLException {
        String deleteDegree_sql =
                "INSERT INTO degree(no,description,remarks) VALUES" +"(?,?,?)";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteDegree_sql);
        //为预编译参数赋值
        pstmt.setString(1,degree.getNo());
        pstmt.setString(2,degree.getDescription());
        pstmt.setString(3,degree.getRemarks());
        //添加学院 并判断是否添加成功
        return pstmt.executeUpdate()>0;
    }

    public boolean delete(Degree degree,Connection connection) throws SQLException {
        String deleteDegree_sql = "delete from degree where id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteDegree_sql);
        //为预编译参数赋值
        pstmt.setInt(1,degree.getId());
        //根据id删除学院 并判断是否删除成功
        return pstmt.executeUpdate()>0;
    }
}

